import csv
from datetime import datetime
import os
import random
import numpy as np
from sqlalchemy.exc import OperationalError, IntegrityError
from service.base_service.logger_service import logger
import pandas as pd
from service.base_service.database_service import DatabaseService


class CsvService:
    def __init__(self):
        # 初始化csv文件的存储路径
        self.csv_send_folder = 'file/csv_perpare_send'
        self.csv_receive_folder = 'file/csv_already_receive'

        # 生成csv文件存储路径
        if not os.path.exists(self.csv_send_folder):
            os.makedirs(self.csv_receive_folder)
        if not os.path.exists(self.csv_receive_folder):
            os.makedirs(self.csv_receive_folder)

    def generate_csv_file(self, data):
        # 提取字段名（键）
        if not data:  # 检查 data 是否为空
            raise ValueError("Data is empty")

        fieldnames = list(data[0].keys())

        # 生成一个随机的五位数
        random_number = random.randint(10000, 99999)
        # 转换为字符串
        random_number_str = str(random_number)
        # 生成带有时间戳的唯一文件名
        timestamp = datetime.now().strftime('%Y-%m-%d_%H-%M-%S')

        csv_file_path = f'{self.csv_send_folder}/{timestamp}_{random_number_str}.csv'

        # 打开 CSV 文件并写入数据
        with open(csv_file_path, 'w', newline='', encoding='utf-8') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

            # 写入格式类型行
            type_row = {key: type(value).__name__ for key, value in data[0].items()}
            writer.writerow(type_row)

            # 写入表头
            writer.writeheader()

            # 写入数据行
            for row in data:
                # 处理 None 值和 datetime 对象
                for key, value in row.items():
                    if value is None:
                        row[key] = ''
                    elif isinstance(value, datetime):
                        row[key] = value.strftime('%Y-%m-%d %H:%M:%S')
                    elif isinstance(value, str) and 'NULL' in value:
                        row[key] = ''
                writer.writerow(row)
        logger.info(f"数据已成功写入 {csv_file_path} 文件")
        return csv_file_path

    def load_data_from_csv(self, csv_file_path, database, table):
        engine = DatabaseService().create_engine(database)

        # 读取CSV文件
        df = pd.read_csv(csv_file_path, encoding='utf8', skiprows=[0])

        # 获取列的格式类型
        types = pd.read_csv(csv_file_path, encoding='utf8', nrows=1).iloc[0].to_dict()

        # 检测哪些列是日期时间类型
        date_columns = []
        for col, dtype in types.items():
            if dtype == 'datetime':
                try:
                    # 尝试将列转换为日期时间类型
                    df[col] = pd.to_datetime(df[col], errors='coerce')
                    date_columns.append(col)
                except ValueError:
                    # 如果转换失败，则认为该列不是日期时间类型
                    pass

        # 处理缺失值
        # 对于日期时间类型的列，使用pd.NaT代替'NULL'
        df[date_columns] = df[date_columns].fillna(pd.NaT)

        # 对于其他非日期时间类型的列，使用np.nan填充
        non_date_columns = df.columns.difference(date_columns)
        df[non_date_columns] = df[non_date_columns].fillna(np.nan)

        # 将DataFrame中的数据插入到MySQL数据库表中
        try:
            df.to_sql(name=table.table_name, con=engine, if_exists='append', index=False)
        except OperationalError as e:
            logger.error(f"Failed to insert data due to an SQL operational error: {e}")
            return {"error": f"SQL operational error: {str(e)}"}
        except IntegrityError as e:
            logger.error(f"Failed to insert data due to a constraint violation: {e}")
            return {"error": f"Constraint violation: {str(e)}"}
        else:
            logger.info("Data inserted successfully.")
            return {"success": "Data inserted successfully."}
        finally:
            # 关闭连接
            engine.dispose()